﻿CREATE TABLE [dbo].[Kqjl_Info] (
    [kqlsh]    BIGINT        IDENTITY (1, 1) NOT NULL,
    [jsdsblsh] INT           NULL,
    [jsdm]     VARCHAR (50)  NULL,
    [sfzh]     VARCHAR (18)  NOT NULL,
    [xm]       VARCHAR (10)  NULL,
    [qybmm]    VARCHAR (100) NULL,
    [gz]       VARCHAR (100) NULL,
    [fbm]      VARCHAR (100) NULL,
    [kh]       VARCHAR (10)  NULL,
    [kbs]      VARCHAR (20)  NULL,
    [kqsj]     DATETIME      NULL,
    [jczt]     BIT           NULL,
    [sfdj]     BIT           NULL,
    [kqlx]     VARCHAR (50)  NULL,
    [yyxp]     VARCHAR (100) NULL,
    [zpxp]     VARCHAR (100) NULL,
    [sfff]     BIT           CONSTRAINT [DF__Kqjl_Info__sfff__75A278F5] DEFAULT ((0)) NULL,
    [jllx]     SMALLINT      CONSTRAINT [DF_Kqjl_Info_jllx] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_KQJL_INFO] PRIMARY KEY CLUSTERED ([kqlsh] ASC)
);


GO
CREATE NONCLUSTERED INDEX [index_kqsj]
    ON [dbo].[Kqjl_Info]([kqsj] ASC);


GO
-- 触发考勤记录插入临时记录表
CREATE trigger [dbo].[tr_KqjlInfoToCopy]
on [dbo].[Kqjl_Info]
for insert
as
set xact_abort on

insert into Kqjl_Info_Copy([jsdm],[sfzh],[xm],[qybmm],[gz],[fbm],[kh],[kbs],[kqsj],[jczt],[sfdj],[kqlx],[yyxp],[zpxp],[sfff],[jllx])
select [jsdm],[sfzh],[xm],[qybmm],[gz],[fbm],[kh],[kbs],[kqsj],[jczt],[sfdj],[kqlx],[yyxp],[zpxp],[sfff],[jllx] from inserted
inner join Jsdsb_Info on(inserted.jsdsblsh = Jsdsb_Info.jsdsblsh) where sfkqjs=1 and jllx=0
GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'记录类型 0：工地进入记录 1：培训记录', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Kqjl_Info', @level2type = N'COLUMN', @level2name = N'jllx';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'1：进 0：出', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Kqjl_Info', @level2type = N'COLUMN', @level2name = N'jczt';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'监视点设备流水号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Kqjl_Info', @level2type = N'COLUMN', @level2name = N'jsdm';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'考勤记录信息(门禁里的记录信息)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Kqjl_Info';

